package ca.ualberta.cs.CLRDB;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import ca.ualberta.cs.DB2ExceptionHandling.DB2SqlException;

public class TextSnapshotsManagement extends EntityRelManagement{

	private static final int SECTION_SIZE_MAX = 200;
	private static final int SNAPSHOT_SIZE_MAX = 1000;
	public TextSnapshotsManagement(){
		mainTableName = "TextSnapshots";
	}
	
	@Override
	public void initialize(Statement statement) throws SQLException {
		
		try{
			statement.executeUpdate("DROP TABLE TextSnapshots");
		}catch(Exception e){}
		
		statement.executeUpdate("CREATE TABLE TextSnapshots ( " +
				"ID INTEGER GENERATED ALWAYS AS " +
					"IDENTITY (START WITH 1 INCREMENT BY 1) NOT NULL, " +
				"section VARCHAR(100), " +
				"resourceId INTEGER, " +
				"snapshot VARCHAR(1000) NOT NULL, " +				
				"CONSTRAINT id_uniq PRIMARY KEY(ID)," +
				"CONSTRAINT snap_uniq UNIQUE(snapshot), " +
				"CONSTRAINT FK_resourceid FOREIGN KEY (resourceId) " +
                	"REFERENCES Resource (ID) ON DELETE CASCADE " +				
		") IN CLRSPACE INDEX IN CLRSPACE"); 
		numOfCols = 4;
		
		statement.executeUpdate("ALTER TABLE TextSnapshots " +
   				"ADD COLUMN " +
   					"previousSection VARCHAR(200) "+
   				"ALTER COLUMN section SET DATA TYPE VARCHAR(200) ");
		
		/*  statement.executeUpdate("ALTER TABLE TextSnapshots " +
	   				"ADD COLUMN " +
	   					"previousSection VARCHAR(200) "+
	   				"ALTER COLUMN section SET DATA TYPE VARCHAR(200) ");
		 */
	    numOfCols = 5;
	    
		/*statement.executeUpdate("CREATE TRIGGER CUTTEXT BEFORE INSERT  " +
				"ON Annotation REFERENCING OLD AS OL " +
				"FOR EACH ROW  MODE DB2SQL " + 
				"WHEN ( OL.replyTo IS NOT NULL ) "+
				"BEGIN ATOMIC " + 
				"DECLARE x INT; " +
				"SET x = (SELECT COUNT(*) FROM Annotation WHERE replyTo = OL.replyTo AND ID != OL.ID); " +
				"IF x = 0 THEN " +
				"UPDATE Annotation SET hasreply = 'n' WHERE ID = OL.replyTo; " +
				"END IF; " +
				"END");*/
						
		
	}
	
	/*public int insertTextSnapshot(int resourceId, String section, String preSection, String snapshot, Statement statement){
		int snapshotId = -1;
		try{
			
			if(section.length() > SECTION_SIZE_MAX )
				section = section.substring(0, SECTION_SIZE_MAX);
			if(preSection.length() > SECTION_SIZE_MAX )
				preSection = preSection.substring(0, SECTION_SIZE_MAX);
			if(snapshot.length() > SNAPSHOT_SIZE_MAX )
				snapshot = snapshot.substring(0, SNAPSHOT_SIZE_MAX);
			
			String keys = "(section, previousSection, resourceId, snapshot)";
			String vals = "('"+section+"', '"+preSection+"', "+resourceId+", '"+snapshot+"')";
			
			statement.executeUpdate("INSERT INTO TextSnapshots "+keys+" VALUES " +vals , Statement.RETURN_GENERATED_KEYS);
			ResultSet resId = statement.getGeneratedKeys();
			resId.next();
			snapshotId = resId.getInt(1);
			   
		} catch (SQLException e) {
			if(e.getErrorCode() == -803 ) //duplicated
			{
				try{
				  ResultSet re = statement.executeQuery("SELECT ID FROM TextSnapshots WHERE snapshot " +
				  		"='"+ snapshot +"'");
				  if(re.next())
					  snapshotId = re.getInt("ID");
				  
				}catch(SQLException e2){
					DB2SqlException.printSqlErrorMsg(e2);
				}
			}
			else{
				DB2SqlException.printSqlErrorMsg(e);
			}
		} catch (Exception e) {			
			e.printStackTrace();
		} 
		return snapshotId;
	}
*/
	
	public int insertTextSnapshot(int resourceId, String section, String preSection, String snapshot, Connection con){
		int snapshotId = -1;
		try{
			
			if(section.length() > SECTION_SIZE_MAX )
				section = section.substring(0, SECTION_SIZE_MAX);
			if(preSection.length() > SECTION_SIZE_MAX )
				preSection = preSection.substring(0, SECTION_SIZE_MAX);
			if(snapshot.length() > SNAPSHOT_SIZE_MAX )
				snapshot = snapshot.substring(0, SNAPSHOT_SIZE_MAX);
			
			String keys = "(section, previousSection, resourceId, snapshot)";
//			String vals = "('"+section+"', '"+preSection+"', "+resourceId+", '"+snapshot+"')";
			
			PreparedStatement statement = con.prepareStatement("INSERT INTO TextSnapshots "+keys+" VALUES (?,?,?,?)",Statement.RETURN_GENERATED_KEYS);
			statement.setString(1, section);
			statement.setString(2, preSection);
			statement.setInt(3, resourceId);
			statement.setString(4,snapshot);
			statement.executeUpdate();
			//statement.executeUpdate("INSERT INTO TextSnapshots "+keys+" VALUES " +vals , Statement.RETURN_GENERATED_KEYS);
			ResultSet resId = statement.getGeneratedKeys();
			resId.next();
			snapshotId = resId.getInt(1);
			   
		} catch (SQLException e) {
			if(e.getErrorCode() == -803 ) //duplicated
			{
				try{
					Statement statement = con.createStatement();
					ResultSet re = statement.executeQuery("SELECT ID FROM TextSnapshots WHERE snapshot " +
				  		"='"+ snapshot +"'");
				  if(re.next())
					  snapshotId = re.getInt("ID");
				  
				}catch(SQLException e2){
					DB2SqlException.printSqlErrorMsg(e2);
				}
			}
			else{
				DB2SqlException.printSqlErrorMsg(e);
			}
		} catch (Exception e) {			
			e.printStackTrace();
		} 
		return snapshotId;
	}

}
